package	com.swinginwind;

import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;



public class JdbcHelper {

	@SuppressWarnings("rawtypes")
	public static List jdbcQueryWithParameters(String connStr, String userName, String password, String sql, Object parameters) throws SQLException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
		String[] strArray = sql.split("\\$");
		StringBuilder resultSqlSB = new StringBuilder();
		if(strArray.length == 1)
			resultSqlSB.append(strArray[0]);
		else
		{
			List<String> parametersArray = new ArrayList<String>();
			List<String> parametersValueArray = new ArrayList<String>();
			for(int i=1;i<strArray.length;i=i+2)
			{
				parametersArray.add(strArray[i]);
				String propertyVal = BeanUtils.getProperty(parameters, strArray[i]);
				parametersValueArray.add(propertyVal);
				resultSqlSB.append(strArray[i-1]).append(propertyVal);
				if(i == strArray.length-2)
					resultSqlSB.append(strArray[i+1]);
			}
		}
		sql = resultSqlSB.toString();
		//System.out.println("ִ�е�SQL���Ϊ:" + sql);
		return JdbcHelper.jdbcQuery(connStr, userName, password, sql);
	}

	@SuppressWarnings("rawtypes")
	public static List jdbcQuery(String connStr, String userName, String password, String sql) throws SQLException {
		java.sql.Connection dbConn = null;
		List results = null;
		try {
			dbConn = java.sql.DriverManager.getConnection(connStr, userName, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			//.printStackTrace();
			throw e;
		}
		QueryRunner qr = new QueryRunner();
		try {
			results = (List)qr.query(dbConn, sql, new MapListHandler());
			dbConn.close();
			//System.out.println("Connection Closed");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();

			try {
				dbConn.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				//e1.printStackTrace();
				throw e1;
			}
			throw e;
		}
		return results;
	}

}
